﻿using System;
using System.Data;
using System.Text;
using NIKE_Project.DAL;

namespace NIKE_Project.BLL
{
    public class SalaryAccountingServer
    {
        //调用DBHelper类
        private readonly DBHelper db = new DBHelper();
        //定义DataSet全局变量
        public DataSet ds;
        /// <summary>
        /// 获取所有的员工
        /// </summary>
        /// <returns></returns>
        public DataTable GetSalesManData(string salesmanName, string role)
        {
            var sql = new StringBuilder();
            sql.Append("select SalesmanName, Role, Mobile, BaseSalary, CommissionRate from Salesan WHERE 1 = 1 ");
            if (!string.IsNullOrEmpty(salesmanName))
                sql.Append($" AND SalesmanName LIKE '%{salesmanName}%'");
            if (!string.IsNullOrEmpty(role))
            {
                sql.Append($" AND Role ='{role}'");
            }
            var dsc = db.getDataSet(sql.ToString());
            return dsc.Tables["nike"];
        }
        /// <summary>
        /// 通过联系方式获取导购员ID
        /// </summary>
        /// <param name="mobile"></param>
        /// <returns></returns>
        public string GetSalesManIdByMobile(string mobile)
        {
            var sqlStr = $"select SalesmanID from Salesan where Mobile='{mobile}'";
            ds = db.getDataSet(sqlStr);
            return ds?.Tables[0]?.Rows[0]["SalesmanID"].ToString();
        }
        ///  <summary>
        /// 计算 导购员月销售额 = 本月度导购员经手的销售金额总和 
        ///  </summary>
        ///  <param name="startTime"></param>
        ///  <param name="endTime"></param>
        ///  <param name="salesMan"></param>
        ///  <returns></returns>
        public DataSet SumShopAmount(DateTime startTime, DateTime endTime, string salesMan)
        {
            var sumShopAmount = $@"select sum(Sales.Amount) from Sales,Salesan 
                                     where Sales.SalesmanID=Salesan.SalesmanID and 
                                     SalesDate between '{startTime}' and '{endTime}' and Sales.SalesmanID='{salesMan}'";
            return db.getDataSet(sumShopAmount);
        }
        /// <summary>
        /// 计算 店长月度销售额 = 店内所有导购员月度销售总和 - 当月销售额考核保底
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public DataSet SumManagerAmount(DateTime startTime, DateTime endTime)
        {
            var sumManagerAmount = $@"select sum(Sales.Amount) from Sales,Salesan 
                                     where Sales.SalesmanID=Salesan.SalesmanID and 
                                     SalesDate between '{startTime}' and '{endTime}'";
            return db.getDataSet(sumManagerAmount);
        }
    }
}
